------------------------------------------------
-- @name: cardinality vs opt_estimate
-- @author: dion cho
-- @description: compare the functionality of cardinality and opt_estimate
-------------------------------------------------

-- create objects
drop table t1 purge;
drop table t2 purge;
create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);

insert into t1
select level, mod(level, 10)
from dual
connect by level <= 10;

insert into t2 
select level, mod(level, 100)
from dual
connect by level <= 10000
;

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');

var b1 number;
var b2 number;

exec :b1 := 1;
exec :b2 := 1;


-- change data a lot
-- what if you can't analyze tables by any reasons?
insert into t1
select level, mod(level, 100)
from dual
connect by level <= 100000
;

-- check data
select count(*) 
from t1
where c2 = :b1
;

-- single table access cardinality estimation
explain plan for
select 
  *
from t1
where c2 = :b1
;

@plan


explain plan for
select /*+ cardinality(t1 100000) */
  *
from t1
where c2 = :b1
;

@plan

explain plan for
select /*+ cardinality(t1 1000) */
  *
from t1
where c2 = :b1
;

@plan


explain plan for
select /*+ opt_estimate(table, t1, scale_rows=1000) */
  *
from t1
where c2 = :b1
;

@plan


-- join cardinality estimation
-- check count
select
  count(*)
from 
  t2, (select * from t1) v
where
  t2.c1 = v.c1 and
  v.c2 = :b1
;


explain plan for
select
  *
from 
  t2, (select * from t1) v
where
  t2.c1 = v.c1 and
  v.c2 = :b1
;

@plan


explain plan for
select
  /*+ cardinality(t1 100) */ 
  *
from 
  t2, (select * from t1) v
where
  t2.c1 = v.c1 and
  v.c2 = :b1
;

@plan


explain plan for
select
  /*+ cardinality(v.t1 100) */ 
  *
from 
  t2, (select * from t1) v
where
  t2.c1 = v.c1 and
  v.c2 = :b1
;

@plan


explain plan for
select
  /*+ opt_estimate(join, (t2, t1), scale_rows=100) */
  *
from 
  t2, (select * from t1) v
where
  t2.c1 = v.c1 and
  v.c2 = :b1
;

@plan


select
  /*+ gather_plan_statistics opt_estimate(join, (t2, t1), scale_rows=100) */
  *
from 
  t2, (select * from t1) v
where
  t2.c1 = v.c1 and
  v.c2 = :b1
;

@stat


explain plan for
select
  /*+ opt_estimate(join, (t2, t1), scale_rows=100) */
  *
from 
  t2, (select /*+ no_merge */ * from t1) v
where
  t2.c1 = v.c1 and
  v.c2 = :b1
;

@plan



explain plan for
select
  /*+ opt_estimate(join, (t2, v), scale_rows=100) */
  *
from 
  t2, (select /*+ no_merge */ * from t1) v
where
  t2.c1 = v.c1 and
  v.c2 = :b1
;

@plan
